﻿
using Castle.Core.Internal;
using Coldairarrow.Business.Interface;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.Interface;
using Coldairarrow.Util;
using Dynamitey;
using Microsoft.VisualBasic;
using Newtonsoft.Json.Linq;
using OfficeOpenXml.Drawing.Slicer.Style;
using org.apache.zookeeper.data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Tracing;
using System.Drawing;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using static AutoMapper.Internal.CollectionMapperExpressionFactory;

namespace Coldairarrow.Business.Interface
{
    public class OL_Rep_PriceManagementInterfaceBusiness : Interface_BaseBusiness, IJob_Business, ITransientDependency
    {
        public List<OL_FilterDTO> Filters;
        public OL_Rep_PriceManagementInterfaceBusiness()
        {
            this.InterfaceName = "电商价格管理表";
            //获取字段的映射关系
            tableField = GetMapField("editRow");
            //索引字段
            Keys = GetKeys("editRow");
            indexFields.Add(Keys.Where(X => X.Type == "K").First().Field);
            //查询过滤条件
            Filters = new List<OL_FilterDTO>();


            //查询执行前的事件
            BeforeQueryEvent += InterfaceBusiness_BeforeQueryEvent;
            //查询执行中的事件
            OnQueryEvent += InterfaceBusiness_OnQueryEvent;
            //执行非查询请求中的事件
            OnNoQueryEvent += InterfaceBusiness_OnNoQueryEvent;

        }

        /// <summary>
        /// 执行非查询请求中时间
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="noQueryEventArgs"></param>
        private void InterfaceBusiness_OnNoQueryEvent(object sender, NoQueryEventArgs noQueryEventArgs)
        {
            //API请求是否成功
            noQueryEventArgs.Success = noQueryEventArgs.Result["success"].ToString().ToBool();
        }

        /// <summary>
        /// 查询执行前的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_BeforeQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //执行查询前添加过滤条件
            if (queryEventArgs.Page.Filters.IsNullOrEmpty() && !Filters.IsNullOrEmpty())
            {
                queryEventArgs.Page.Filters = Filters;
            }

        }

        /// <summary>
        /// 查询执行中的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_OnQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //是否查询完成
            if (queryEventArgs.Result["data"]["rows"].ToList().Count == 0)
            {
                queryEventArgs.Page.Complete = true;
            }
            //向总行中添加新的行
            queryEventArgs.Rows.AddRange(queryEventArgs.Result["data"]["rows"].ToList());
            //获取总页数
            queryEventArgs.Page.Total = queryEventArgs.Result["data"]["total"].ToString().ToInt();
            //获取查询成功标志
            queryEventArgs.Success = queryEventArgs.Result["success"].ToString().ToBool();
        }


        /// <summary>
        /// 执行增删改查业务
        /// </summary>
        public async void ExecuteInterface()
        {

            try
            {
                //数据库连接
                ConString = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                SqlDb = (SqlServerHelper)DbHelperFactory.GetDbHelper(EFCore.Sharding.DatabaseType.SqlServer, ConString);

                //获取此接口的所有API信息
                this.interfaceOption = SqlDb.GetListBySql<Base_InterfaceOption>(@$"SELECT * FROM  Base_InterfaceOption(NOLOCK) WHERE Name='{InterfaceName}' ");
                Base_InterfaceOption queryOption = this.interfaceOption.Where(o => o.OperateType == "getFilterRows").First();//查询API配置
                Base_InterfaceOption insertOption = this.interfaceOption.Where(o => o.OperateType == "addRow").First();//新增API配置
                Base_InterfaceOption upDateOption = this.interfaceOption.Where(o => o.OperateType == "editRow").First();//更行API配置
                Base_InterfaceOption deleteOption = this.interfaceOption.Where(o => o.OperateType == "deleteRow").First();//删除API配置
                //获取开始日期
                //string dateForm = DateTime.Now.AddDays(-queryOption.ExcInterval).ToString("yyyy-MM-dd");



                //获取One_Link亚马逊MSKU信息
                AMZ_MSKUInterfaceBusiness msku = new AMZ_MSKUInterfaceBusiness();
                msku.Filters.Clear();
                msku.Filters.Add(
                   //13:>,14>=15:<,16<=
                   new OL_FilterDTO() { ControlId = "685aa0b5a31d84c7f659fa16", DataType = 30, SpliceType = 1, FilterType = 2, Value = "10001" }//只查询亚马逊的MSKU
               );

                List<AMZ_MSKU> AMZ_MSKUList = await msku.QueryAsync<AMZ_MSKU>(msku.queryOption, msku.tableField, msku.SqlDb);
                AMZ_MSKUList.ForEach(x =>
                {
                    //SKU
                    List<AssTable> table = x.sku_lock.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.sku_lock_T = table[0];
                    x.sku_lock = x.sku_lock_T.IsNullOrEmpty() ? "" : x.sku_lock_T.sid;
                    //店铺
                    table = x.shop.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.shop_T = table[0];
                    x.shop = x.shop_T.IsNullOrEmpty() ? "" : x.shop_T.sid;
                });

                //获取One_Link SKU信息
                //     OL_LocalSKUInterfaceBusiness localSKU = new OL_LocalSKUInterfaceBusiness();
                //     localSKU.Filters.Clear();
                //     localSKU.Filters.Add(
                //    //13:>,14>=15:<,16<=
                //    new OL_FilterDTO() { ControlId = "", DataType = 15, SpliceType = 1, FilterType = 14, Value = "" }//FDATE字段过滤日开始700天前到现在的数据
                //);
                //     List<LocalSKU> localSKUList = await localSKU.QueryAsync<LocalSKU>(localSKU.queryOption, localSKU.tableField, localSKU.SqlDb);


                //获取店铺
                OL_ShopInterfaceBusiness OL_Shop = new OL_ShopInterfaceBusiness();
                OL_Shop.Filters.Add(
            new OL_FilterDTO() { ControlId = "", DataType = 29, SpliceType = 1, FilterType = 8, Value = "" }
                //new OL_FilterDTO() { ControlId = "661d1c34387bc49b7a55d06e", DataType = 15, SpliceType = 1, FilterType = 14, Value = "2024-11-24" }
                );
                List<OL_Shop> shopList = OL_Shop.Query<OL_Shop>(OL_Shop.queryOption, OL_Shop.tableField, OL_Shop.SqlDb);
                shopList.ForEach(x =>
                {
                    //店铺
                    List<AssTable> table = x.Platform_.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.Platform_ = table.IsNullOrEmpty() ? "" : table.First().sid;
                });
                List<OL_Rep_PriceManagement> saleskLsit = new List<OL_Rep_PriceManagement>();

                //获取亚马逊销售数据
                List<OL_Rep_PriceManagement> sales = GetAMZPrice(AMZ_MSKUList, shopList);
                saleskLsit.AddRange(sales);



                List<OL_Rep_PriceManagement> sourceData = new List<OL_Rep_PriceManagement>();
                sourceData = saleskLsit;






                //获取目标数据
                Filters.Clear();
                Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "", DataType = 15, SpliceType = 1, FilterType = 14, Value = "" }//FDATE字段过滤日开
            );
                List<OL_Rep_PriceManagement> targetData = await QueryAsync<OL_Rep_PriceManagement>(queryOption, tableField, SqlDb);
                foreach (var item1 in targetData)
                {
                    //MSKU
                    List<AssTable> table = item1.MSKU.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) item1.MSKU = table[0].sid;
                    else item1.MSKU = "";
                    //SKU
                    table = item1.SKU.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) item1.SKU = table[0].sid;
                    else item1.SKU = "";
                    //店铺
                    table = item1.Seller.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) item1.Seller = table[0].sid;
                    else item1.Seller = "";
                }
                if (this.State > 0 && sourceData.Count > 0)
                {
                    //更新
                    #region
                    List<OL_Rep_PriceManagement> updataItem = sourceData.Intersect(targetData, new ComparerFromKeys<OL_Rep_PriceManagement>(Keys, "RowId")).ToList();//获取交集(更新项)
                    List<OL_RowDTO> updataData = ConvertOL_Row(updataItem, tableField);//需要更新的数据集合
                    ExecuteNoQuery<OL_RowDTO>(updataData, upDateOption, SqlDb);//执行更新
                    #endregion

                    //新增
                    #region
                    List<OL_Rep_PriceManagement> insertItem = sourceData.Except(targetData, new ComparerFromKey<OL_Rep_PriceManagement>(indexFields.First(), "RowId")).ToList();//获取源数据对目标数据的差集(新增项)
                    List<OL_RowDTO> insertData = ConvertOL_Row(insertItem, tableField);//需要新增的数据集合
                    ExecuteNoQuery<OL_RowDTO>(insertData, insertOption, SqlDb);//执行插入行
                    #endregion

                    //删除
                    #region
                    List<OL_Rep_PriceManagement> deleteItem = targetData.Except(sourceData, new ComparerFromKey<OL_Rep_PriceManagement>(indexFields.First(), "RowId")).ToList();//获取目标数对据源数据的差集(删除项)
                    List<OL_Rep_PriceManagement> repeatItem = targetData.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                    //将重复的项也一起加入删除的列表
                    deleteItem.AddRange(repeatItem);
                    //筛选出删除列表中重复的项
                    var temp = deleteItem.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                    deleteItem.AddRange(temp);
                    List<OL_RowDTO> deleteData = ConvertOL_Row(deleteItem, tableField);//需要删除的数据集合
                    ExecuteNoQuery<OL_RowDTO>(deleteData, deleteOption, SqlDb);//执行删除行
                    #endregion
                }
            }


            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, InterfaceName, "erro", "");
            }
        }




        /// <summary>
        /// 将源数据根据字典映射关系转换成OL_RowDTO
        /// </summary>
        /// <typeparam name="T">源数据</typeparam>
        /// <param name="sourceData">源数据</param>
        /// <param name="map">Key为目标字段名称，Value为源字段名称</param>
        /// <returns></returns>
        public List<OL_RowDTO> ConvertOL_Row<T>(List<T> sourceData, Dictionary<string, string> map)
        {
            List<OL_RowDTO> list = new List<OL_RowDTO>();
            object rowId;
            foreach (var item in sourceData)
            {
                rowId = item.GetPropertyValue("RowId");
                //需要新增的数据
                OL_RowDTO row = new OL_RowDTO() { RowId = rowId.IsNullOrEmpty() ? null : rowId.ToString(), Controls = new List<Control>() };
                foreach (var c in map)
                {
                    //获取字段的键值做判断
                    var field = Keys.Where(x => x.TField == c.Key).ToList();
                    string isKey = "";
                    if (field.Count > 0) isKey = field.First().Type;
                    //判断对象的字段是否存在
                    if (item.ContainsProperty(c.Value) && isKey != "N")
                    {
                        //存在则添加数据
                        row.Controls.Add(new Control() { ControlId = c.Key, Value = item.GetPropertyValue(c.Value) });
                    }
                }
                list.Add(row);
            }
            return list;
        }

        public List<OL_Rep_PriceManagement> GetAMZPrice(List<AMZ_MSKU> mskuList, List<OL_Shop> shopList)
        {

            List<OL_Rep_PriceManagement> dataList = new List<OL_Rep_PriceManagement>();
            try
            {


                //获取亚马逊报表
                List<PrdPerformance> amzSalesList = SqlDb.GetListBySql<PrdPerformance>(@$"SELECT seller_sku,sid,seller_name,OL_SKU,OL_MSKU,Date,volume,return_goods_count,amount_dollar
FROM SD_LX_PrdPerformance WHERE  volume >0 AND Date>='{DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd")}'");
                //获取MSKU最新售价
                List<PrdPerformance> amzPriceList = SqlDb.GetListBySql<PrdPerformance>(@$"WITH RankedEmployees AS (
SELECT  
seller_sku,
sid,
seller_name,
OL_SKU,OL_MSKU,
Date,
volume,
promotion_discount_dollar,
amount_dollar , 
CASE 
WHEN volume>0 THEN (Convert(decimal(15,2),amount_dollar) /volume)  
WHEN volume=0 THEN 0
END  price,
ROW_NUMBER() OVER (PARTITION BY seller_sku,sid ORDER BY date DESC) AS RowNum
FROM SD_LX_PrdPerformance WHERE  volume>0
)
SELECT * FROM  RankedEmployees WHERE  RowNum = 1;");

                //获取集团库存
                List<Rep_OrgInventory> orgInvList = SqlDb.GetListBySql<Rep_OrgInventory>(@$"SELECT * FROM  SD_OL_OrgInventory 
WHERE WarehouseType ='亚马逊FBA' OR WarehouseType ='九方仓' OR WarehouseType ='众包' OR WarehouseType ='豹仓三方仓' OR WarehouseType ='BGA自建仓'  OR WarehouseType ='橙联' ");



                foreach (var item1 in mskuList)
                {

                    OL_Rep_PriceManagement data = new OL_Rep_PriceManagement();
                    data.SKU = item1.sku_lock;
                    data.MSKU = item1.RowId;
                    data.MSKU_S = item1.seller_sku;
                    var shop = shopList.Where(x => x.ID == item1.sid);
                    if (!shop.IsNullOrEmpty())
                    {
                        data.Seller = shop.FirstOrDefault().RowId;
                    }
                    //获取最新售价
                    var firstPirce = amzPriceList.Where(x => x.seller_sku == item1.seller_sku && x.sid == item1.sid);
                    if (!firstPirce.IsNullOrEmpty() && firstPirce.Count() > 0)
                    {
                        var first = firstPirce.First();
                        data.SalesDate = first.date;
                        data.OrderPrice = first.price;
                        data.Coupon = first.promotion_discount_dollar;
                    }
                    //获取近期销售数据
                    var sales = amzSalesList.Where(x => x.seller_sku == item1.seller_sku && x.sid == item1.sid);
                    if (!sales.IsNullOrEmpty() && sales.Count() > 0)
                    {
                        //30天销量销售额
                        data.SalesThat30Day = sales.Sum(x => x.volume.ToInt()).ToString();
                        data.SalesAmountThat30Day = sales.Sum(x => x.amount_dollar.ToDouble()).ToString();
                        data.ReturnThat30Day = sales.Sum(x => x.return_goods_count.ToInt()).ToString();
                        //7天销量销售额
                        var salesIn7Days = sales.Where(x => x.date.ToDateTime() >= DateTime.Now.AddDays(-7).Date);
                        if (!salesIn7Days.IsNullOrEmpty() && salesIn7Days.Count() > 0)
                        {
                            data.SalesThat7Day = salesIn7Days.Sum(x => x.volume.ToInt()).ToString();
                            data.SalesAmountThat7Day = salesIn7Days.Sum(x => x.amount_dollar.ToDouble()).ToString();
                            data.ReturnThat7Day = salesIn7Days.Sum(x => x.return_goods_count.ToInt()).ToString();
                        }
                        //昨天销量销售额
                        var salesIn1Yesterday = sales.Where(x => x.date.ToDateTime() >= DateTime.Now.AddDays(-1).Date);
                        if (!salesIn1Yesterday.IsNullOrEmpty() && salesIn1Yesterday.Count() > 0)
                        {
                            data.SalThatDay = salesIn1Yesterday.Sum(x => x.volume.ToInt()).ToString();
                            data.SalesAmountThatDay = salesIn1Yesterday.Sum(x => x.amount_dollar.ToDouble()).ToString();
                        }
                    }


                    //获取FBA库存数据
                    var fbaInv = orgInvList.Where(x => x.PrdCode == item1.seller_sku && x.WarehouseType == "亚马逊FBA");
                    if (!fbaInv.IsNullOrEmpty() && fbaInv.Count() > 0)
                    {
                        data.FBAInv = fbaInv.Sum(x => x.InInvQty).ToString();
                        data.FBAInTransit = fbaInv.Sum(x => x.InTransitQty).ToString();
                    }


                    //获取易仓库存数据
                    var ecInv = item1.sku_lock_T.IsNullOrEmpty() ? null : orgInvList.Where(x => x.PrdCode == item1.sku_lock_T.name && x.WarehouseType != "亚马逊FBA");
                    if (!ecInv.IsNullOrEmpty() && ecInv.Count() > 0)
                    {
                        data.ECInv = ecInv.Sum(x => x.InInvQty).ToString();
                        data.ECInTransit = ecInv.Sum(x => x.InTransitQty).ToString();
                    }

                    data.ID = data.MSKU_S + item1.sid;
                    dataList.Add(data);

                }
            }
            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, InterfaceName, "erro", "");
            }
            return dataList;
        }

        /// <summary>
        /// 任务
        /// </summary>
        public void Job()
        {
            ExecuteInterface();
        }
    }

}
